package com.bluesky.light.staff;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.bluesky.light.util.DB;

/**
 * 醫療人員對數據庫增刪查改等, 對數據庫的基本操作
 * 
 * @author
 * 
 */
public class StaffDAO {

	/**
	 * @return
	 * 
	 *         從數據庫取出所有醫療人員的個人資料
	 */
	public List<Staff> getStaffs() {

		List<Staff> staffs = new ArrayList<Staff>();

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn = DB.getConn();
			stmt = DB.getStatement(conn);
			rs = DB.getResultSet(stmt, "select * from t_staff");

			while (rs.next()) {
				Staff s = getStaffFromRs(rs);
				staffs.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}

		return staffs;
	}

	private Staff getStaffFromRs(ResultSet rs) {
		Staff s = null;

		try {
			s = new Staff();
			s.setId(rs.getInt("id"));
			s.setIdcard(rs.getString("idcard"));
			s.setUsername(rs.getString("username"));
			s.setName(rs.getString("name"));
			s.setPwd(rs.getString("pwd"));
			s.setDeptno(rs.getInt("deptno"));
			s.setJob(rs.getString("job"));
			s.setSal(rs.getDouble("sal"));
			s.setMgr(rs.getInt("mgr"));
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return s;

	}

	/**
	 * @param s
	 * 
	 *            在數據庫增加1個醫療人員
	 */
	public void add(Staff s) {

		Connection conn = null;
		String sql = "insert into t_staff (username, pwd, name, idcard, deptno, job, sal, mgr) "
				+ "values (?, ?, ?, ?, ?, ?, ?, ?)";
		PreparedStatement pstmt = null;

		try {
			conn = DB.getConn();
			pstmt = DB.prepare(conn, sql);

			pstmt.setString(1, s.getUsername());
			pstmt.setString(2, s.getPwd());
			pstmt.setString(3, s.getName());
			pstmt.setString(4, s.getIdcard());
			pstmt.setInt(5, s.getDeptno());
			pstmt.setString(6, s.getJob());
			pstmt.setDouble(7, s.getSal());
			pstmt.setInt(8, s.getMgr());

			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}

	/**
	 * @param id
	 * 
	 *            根據 id 刪除醫療人員
	 */
	public void delete(int id) {
		Connection conn = null;
		String sql = "delete from t_staff where id = " + id;
		Statement stmt = null;

		try {
			conn = DB.getConn();
			stmt = DB.getStatement(conn);
			// System.out.println(conn);
			DB.excuteUpdate(stmt, sql);
		} finally {
			DB.close(stmt);
			DB.close(conn);
		}
	}

	/**
	 * @param username
	 * @param pwd
	 * @return s
	 * @throws StaffNotFoundException
	 * @throws StaffPasswordNotCorrectException
	 * 
	 *             根據醫療人員輸入的用戶名和密碼, 查看數據庫有沒有輸入的 user name, 並驗證輸入的密碼是否正確
	 */
	public Staff check(String username, String pwd)
			throws StaffNotFoundException, StaffPasswordNotCorrectException {

		Staff s = null;

		Connection conn = DB.getConn();
		Statement stmt = DB.getStatement(conn);
		String sql = "select * from t_staff where username = '" + username + "'";
		ResultSet rs = DB.getResultSet(stmt, sql);

		try {
			if (!rs.next()) {
				throw new StaffNotFoundException("User not found:" + username);
			} else {
				if (!pwd.equals(rs.getString("pwd"))) {
					throw new StaffPasswordNotCorrectException(
							"Password Not Correct!");
				}
				s = getStaffFromRs(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}

		return s;
	}

	/**
	 * @param id
	 * @return
	 * 
	 *         根據 id 取出醫護人員的個人資料
	 */
	public Staff loadById(int id) {
		Staff s = new Staff();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		conn = DB.getConn();
		String sql = "select * from t_staff where id = " + id;
		stmt = DB.getStatement(conn);
		rs = DB.getResultSet(stmt, sql);

		try {
			if (rs.next()) {
				s.setId(rs.getInt("id"));
				s.setUsername(rs.getString("username"));
				s.setPwd(rs.getString("pwd"));
				s.setName(rs.getString("name"));
				s.setIdcard(rs.getString("idcard"));
				s.setDeptno(rs.getInt("deptno"));
				s.setJob(rs.getString("job"));
				s.setSal(rs.getDouble("sal"));
				s.setMgr(rs.getInt("mgr"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}

		return s;

	}
	
	/**
	 * @param s
	 *            更改醫療人員的個人資料
	 */
	public void update(Staff s) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		conn = DB.getConn();
		String sql = "update t_staff set name = ?, idcard = ?, deptno = ?, job = ?, sal= ?, mgr= ? where id = ?";
		pstmt = DB.prepare(conn, sql);
		try {
			pstmt.setString(1, s.getName());
			pstmt.setString(2, s.getIdcard());
			pstmt.setInt(3, s.getDeptno());
			pstmt.setString(4, s.getJob());
			pstmt.setDouble(5, s.getSal());
			pstmt.setDouble(6, s.getMgr());
			pstmt.setInt(7, s.getId());

			pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}

	/**
	 * @param s
	 *            更改醫療人員的密碼
	 */
	public void updatePwd(Staff s) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		conn = DB.getConn();
		String sql = "update t_staff set pwd = ? where id = ?";
		pstmt = DB.prepare(conn, sql);

		try {
			pstmt.setString(1, s.getPwd());
			pstmt.setInt(2, s.getId());

			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}

	}
}
